package db.tables;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import org.hsqldb.Types;

import db.DBObject;

public class DBRequestTable extends DBObject {
	public static final String TABLE_NAME="request";
	
	public static final String REQUEST_ID_COL = TABLE_NAME+"_id",
			SENDER_ID_COL = "sender_id", EDITOR_ID_COL = "editor_id",
			DATE_COL=TABLE_NAME+"_date",
			CATEGORY_ID_COL= DBCategoryTable.CATEGORY_ID_COL,
			STATUS_ID_COL = "status_id",
			REQUEST_TITLE_COL = TABLE_NAME+"_title", 
			REQUEST_TEXT_COL=TABLE_NAME+"_text";

	public DBRequestTable() throws ClassNotFoundException, SQLException {
		super(TABLE_NAME, new Object[][] {
				{ REQUEST_ID_COL, Types.BIGINT, SQL_IDENTITY_PRIMARY_KEY, "" },
				{ SENDER_ID_COL, Types.BIGINT, "", "" },
				{ EDITOR_ID_COL, Types.BIGINT, "", ""},
				{ CATEGORY_ID_COL, Types.BIGINT, "", 0L},
				{ STATUS_ID_COL, Types.BIGINT, "", 0L},
				{ DATE_COL, Types.DATE,"",SQL_CURRENT_DATE},
				{ REQUEST_TITLE_COL, Types.VARCHAR, "", "" },
				{ REQUEST_TEXT_COL, Types.VARCHAR, 
					getSQLForeignKeyReference(DBCategoryTable.TABLE_NAME, STATUS_ID_COL, DBCategoryTable.CATEGORY_ID_COL)
				+getSQLForeignKeyReference(DBUserTable.TABLE_NAME, SENDER_ID_COL, DBUserTable.USER_ID_COL)
				+getSQLForeignKeyReference(DBUserTable.TABLE_NAME, EDITOR_ID_COL, DBUserTable.USER_ID_COL), "" }});
	}
	
	public ArrayList<ArrayList<Object>> getArrayListByStatusOrderBy(Long status_id, String order_column){
		ArrayList<ArrayList<Object>> result=null;
		ResultSet rs=null;
		try {
			rs=getSimpleSelectResults(
					new String[]{REQUEST_ID_COL,SENDER_ID_COL,EDITOR_ID_COL,CATEGORY_ID_COL,STATUS_ID_COL,DATE_COL,REQUEST_TITLE_COL,REQUEST_TEXT_COL}, 
					new String[]{STATUS_ID_COL}, new Object[]{status_id}, new String[]{tableName}, null, null, order_column, SQL_ORDER_DESCENDING);
			result=getResultSetAsArrayList(rs);
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
		return result;
	}
}
